﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using ZLDC.Common.Estate;

namespace ZLDC.DAL.Estate
{
                public class QuantificationDAL
    {
                                                                                                                                                                                                                                                public static void p_quantification_insert(int typeid, int tableId, int deptId, string dept, int salesmanId, string salesman, int hfollow, int cfollow, int look, int looked, int newhousing, int newcustomer, int only, int versa, int key, int telphone, int hasphoto, int photo, int self, int mark1, int mark2, int mark3, int mark4, int mark5, out string outmsg, out int newid, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@typeid", typeid));
            parms.Add(new SqlParameter("@tableId", tableId));
            parms.Add(new SqlParameter("@deptId", deptId));
            parms.Add(new SqlParameter("@dept", dept));
            parms.Add(new SqlParameter("@salesmanId", salesmanId));
            parms.Add(new SqlParameter("@salesman", salesman));
            parms.Add(new SqlParameter("@hfollow", hfollow));
            parms.Add(new SqlParameter("@cfollow", cfollow));
            parms.Add(new SqlParameter("@look", look));
            parms.Add(new SqlParameter("@looked", looked));
            parms.Add(new SqlParameter("@newhousing", newhousing));
            parms.Add(new SqlParameter("@newcustomer", newcustomer));
            parms.Add(new SqlParameter("@only", only));
            parms.Add(new SqlParameter("@versa", versa));
            parms.Add(new SqlParameter("@key", key));
            parms.Add(new SqlParameter("@telphone", telphone));
            parms.Add(new SqlParameter("@hasphoto", hasphoto));
            parms.Add(new SqlParameter("@photo", photo));
            parms.Add(new SqlParameter("@self", self));
            parms.Add(new SqlParameter("@mark1", mark1));
            parms.Add(new SqlParameter("@mark2", mark2));
            parms.Add(new SqlParameter("@mark3", mark3));
            parms.Add(new SqlParameter("@mark4", mark4));
            parms.Add(new SqlParameter("@mark5", mark5));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);

            SqlParameter pNewid = new SqlParameter("@newid", SqlDbType.Int, 4);
            pNewid.Direction = ParameterDirection.Output;
            parms.Add(pNewid);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_quantification_insert", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);
            newid = (pNewid.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pNewid.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }

        public static void p_quantification_insert2(int deptId, string dept, int salesmanId, string salesman, int hfollow, int cfollow, int look, float looked, int newhousing, int newcustomer, int only, int versa, int key, int telphone, int hasphoto, int photo, int self, int mark1, int mark2, float mark3, int mark4, int mark5, out string outmsg, out int newid, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@typeid", 0));
            parms.Add(new SqlParameter("@tableId", 0));
            parms.Add(new SqlParameter("@deptId", deptId));
            parms.Add(new SqlParameter("@dept", dept));
            parms.Add(new SqlParameter("@salesmanId", salesmanId));
            parms.Add(new SqlParameter("@salesman", salesman));
            parms.Add(new SqlParameter("@hfollow", hfollow));
            parms.Add(new SqlParameter("@cfollow", cfollow));
            parms.Add(new SqlParameter("@look", look));
            parms.Add(new SqlParameter("@looked", looked));
            parms.Add(new SqlParameter("@newhousing", newhousing));
            parms.Add(new SqlParameter("@newcustomer", newcustomer));
            parms.Add(new SqlParameter("@only", only));
            parms.Add(new SqlParameter("@versa", versa));
            parms.Add(new SqlParameter("@key", key));
            parms.Add(new SqlParameter("@telphone", telphone));
            parms.Add(new SqlParameter("@hasphoto", hasphoto));
            parms.Add(new SqlParameter("@photo", photo));
            parms.Add(new SqlParameter("@self", self));
            parms.Add(new SqlParameter("@mark1", mark1));
            parms.Add(new SqlParameter("@mark2", mark2));
            parms.Add(new SqlParameter("@mark3", mark3));
            parms.Add(new SqlParameter("@mark4", mark4));
            parms.Add(new SqlParameter("@mark5", mark5));



            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);

            SqlParameter pNewid = new SqlParameter("@newid", SqlDbType.Int, 4);
            pNewid.Direction = ParameterDirection.Output;
            parms.Add(pNewid);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_quantification_insert2", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);
            newid = (pNewid.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pNewid.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }
        public static void p_quantification_insert(int deptId, string dept, int salesmanId, string salesman, int hfollow, int cfollow, int look, int looked, int newhousing, int newcustomer, int only, int versa, int key, int telphone, int hasphoto, int photo, int self, int mark1, int mark2, int mark3, int mark4, int mark5, out string outmsg, out int newid, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@typeid", 0));
            parms.Add(new SqlParameter("@tableId", 0));
            parms.Add(new SqlParameter("@deptId", deptId));
            parms.Add(new SqlParameter("@dept", dept));
            parms.Add(new SqlParameter("@salesmanId", salesmanId));
            parms.Add(new SqlParameter("@salesman", salesman));
            parms.Add(new SqlParameter("@hfollow", hfollow));
            parms.Add(new SqlParameter("@cfollow", cfollow));
            parms.Add(new SqlParameter("@look", look));
            parms.Add(new SqlParameter("@looked", looked));
            parms.Add(new SqlParameter("@newhousing", newhousing));
            parms.Add(new SqlParameter("@newcustomer", newcustomer));
            parms.Add(new SqlParameter("@only", only));
            parms.Add(new SqlParameter("@versa", versa));
            parms.Add(new SqlParameter("@key", key));
            parms.Add(new SqlParameter("@telphone", telphone));
            parms.Add(new SqlParameter("@hasphoto", hasphoto));
            parms.Add(new SqlParameter("@photo", photo));
            parms.Add(new SqlParameter("@self", self));
            parms.Add(new SqlParameter("@mark1", mark1));
            parms.Add(new SqlParameter("@mark2", mark2));
            parms.Add(new SqlParameter("@mark3", mark3));
            parms.Add(new SqlParameter("@mark4", mark4));
            parms.Add(new SqlParameter("@mark5", mark5));



            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);

            SqlParameter pNewid = new SqlParameter("@newid", SqlDbType.Int, 4);
            pNewid.Direction = ParameterDirection.Output;
            parms.Add(pNewid);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_quantification_insert", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);
            newid = (pNewid.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pNewid.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }


        public static void p_quantification_insert1(int typeid, int tableId, int deptId, string dept, int salesmanId, string salesman, int hfollow, int cfollow, int look, int looked, int newhousing, int newcustomer, int only, int versa, int key, int telphone, int hasphoto, int photo, int self, int mark1, int mark2, int mark3, int mark4, int mark5,int visit,int handbill,int EarnestMoney,int talks,int afterservice, out string outmsg, out int newid, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@typeid", typeid));
            parms.Add(new SqlParameter("@tableId", tableId));
            parms.Add(new SqlParameter("@deptId", deptId));
            parms.Add(new SqlParameter("@dept", dept));
            parms.Add(new SqlParameter("@salesmanId", salesmanId));
            parms.Add(new SqlParameter("@salesman", salesman));
            parms.Add(new SqlParameter("@hfollow", hfollow));
            parms.Add(new SqlParameter("@cfollow", cfollow));
            parms.Add(new SqlParameter("@look", look));
            parms.Add(new SqlParameter("@looked", looked));
            parms.Add(new SqlParameter("@newhousing", newhousing));
            parms.Add(new SqlParameter("@newcustomer", newcustomer));
            parms.Add(new SqlParameter("@only", only));
            parms.Add(new SqlParameter("@versa", versa));
            parms.Add(new SqlParameter("@key", key));
            parms.Add(new SqlParameter("@telphone", telphone));
            parms.Add(new SqlParameter("@hasphoto", hasphoto));
            parms.Add(new SqlParameter("@photo", photo));
            parms.Add(new SqlParameter("@self", self));
            parms.Add(new SqlParameter("@mark1", mark1));
            parms.Add(new SqlParameter("@mark2", mark2));
            parms.Add(new SqlParameter("@mark3", mark3));
            parms.Add(new SqlParameter("@mark4", mark4));
            parms.Add(new SqlParameter("@mark5", mark5));
            parms.Add(new SqlParameter("@visit",visit));
            parms.Add(new SqlParameter("@handbill",visit));
            parms.Add(new SqlParameter("@EarnestMoney",visit));
            parms.Add(new SqlParameter("@talks",visit));
            parms.Add(new SqlParameter("@afterservice",visit));


            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);

            SqlParameter pNewid = new SqlParameter("@newid", SqlDbType.Int, 4);
            pNewid.Direction = ParameterDirection.Output;
            parms.Add(pNewid);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_quantification_insert1", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);
            newid = (pNewid.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pNewid.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }


                                                                public static int UpdateQuantification(int id, string[] fiednames, _Database db)
        {
            int rows = 0;

            try
            {
                if (fiednames.Length > 0)
                {
                    string sql = "update t_quantification set ";
                    for (int i = 0; i < fiednames.Length; i++)
                    {

                        if (i > 0)
                        {
                            sql += ", ";
                        }
                        sql += fiednames[i] + "+=1";
                    }
                    sql += " where id=" + id;
                    rows = db.ExecuteSql(sql);
                }
            }
            catch { }
            finally { db.close(); }

            return rows;
        }
        public static int UpdateQuantification(int id,float total,int cfollow,string[] fiednames, _Database db)
        {
            int rows = 0;

            try
            {
                if (fiednames.Length > 0)
                {
                    string sql = "update t_quantification set ";
                    for (int i = 0; i < fiednames.Length; i++)
                    {
                        if (fiednames[i].ToString() == "cfollow") 
                        {
                            total = cfollow;
                        }
                        if (i > 0)
                        {
                            sql += ", ";
                        }
                        sql += fiednames[i] + "+=" + total + "";
                    }
                    sql += " where id=" + id;
                    rows = db.ExecuteSql(sql);
                }
            }
            catch { }
            finally { db.close(); }

            return rows;
        }
        
                                                                                                                public static int UpdateQuantification(int typeid, int tableId, int userId, int deptId, string deptName, string userName, int id, string[] fiednames, _Database db)
        {
            int rows = 0;

            try
            {
                if (fiednames.Length > 0)
                {
                    string sql = "update t_quantification set ";
                    for (int i = 0; i < fiednames.Length; i++)
                    {

                        if (i > 0)
                        {
                            sql += ", ";
                        }
                        sql += fiednames[i] + "+=1";
                    }
                    sql += " where id=" + id + ";";

                                        string tableName = "";
                    if (typeid == 5 || typeid == 14 || typeid == 7 || typeid == 8 || typeid == 9 || typeid == 16 || typeid==19)
                        tableName = "t_housing";
                    else if (typeid == 6 || typeid == 15 || typeid == 17 || typeid == 18  || typeid==21 || typeid==22 || typeid==23)
                        tableName = "t_customer";

                    if (typeid == 5 || typeid == 14 || typeid == 7 || typeid == 8 || typeid == 9 || typeid == 6 || typeid == 15 || typeid == 17 || typeid == 18 || typeid == 19 || typeid == 21 || typeid == 22 || typeid == 23)
                    {
                                                sql += " insert into t_quantificationLog(typeid, userid, username, deptid, deptName, tableid, tableName) values(" + @typeid + ", " + @userId + ",'" + @userName + "', " + @deptId + ", '" + deptName + "', " + tableId + ", '" + tableName + "')";
                    }

                    rows = db.ExecuteSql(sql);
                }
            }
            catch { }
            finally { db.close(); }

            return rows;
        }

                                                                public static int isExit(string date, int userId, _Database db)
        {
            int ID = 0;
            try
            {
                string sql = "select ID from  t_quantification  where countDate='" + date + "' and salesmanId='" + userId + "'";
                DataSet ds = db.Query(sql);
                ID = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            }
            catch { }
            finally { db.close(); }
            return ID;
        }


        public static int isExitByTypeId(int typeid,int Id, _Database db)
        {
            int count = 0;
            try
            {
                string sql = "select count(*) from t_quantificationLog where typeid=" + typeid + " and userId=" + _Utility.GetInfoBySession().m_sUserID + " and tableId="+Id+" and DATEDIFF(DAY,countDate,GETDATE())=0";
                DataSet ds = db.Query(sql);
                count =Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            }
            catch { }
            finally { db.close(); }
            
            return count;
        }

                                                                                public static DataTable p_quantification_list_table(int page, int pagesize, string condition, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_quantification_list", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }
        
    }
}
